﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DbDoc.BusinessObjects;
using System.Configuration;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;

using System.Configuration.Provider;
using Microsoft.SqlServer.Management.Common;
using DbDoc.Provider.Source;
using DbDoc.Provider.Persistence;

namespace DbDoc.Provider.Source.Sql
{
    public class SqlSourceProvider : SourceProvider
    {
        private string targetConnectionString;
        private List<string> includeDatabases = new List<string>();
        private List<string> excludeDatabases = new List<string>();

        #region Properties
        public override string ConnectionInfo
        {
            get { return targetConnectionString; }
        }

        public override string StorageName
        {
            get
            {
                if (string.IsNullOrWhiteSpace(ConnectionInfo))
                    return string.Empty;
                string[] settings = ConnectionInfo.Split(new char[] { ';' });

                var server = (from s in settings
                              where s.Split(new char[] { '=' })[0].ToLower() == "server"
                              select s.Split(new char[] { '=' })[1]).FirstOrDefault();
                return server;
            }
        }

        public override IEnumerable<string> ExcludedDatabasesList
        {
            get { return excludeDatabases; }
        }
        #endregion


        public override void Initialize()
        {
            Setting connectionString = PersistenceProviderManager.Provider.GetByKey(Constants.Settings.SOURCE_CONNECTIONSTRING);
            if (connectionString == null)
                throw new ConfigurationException("The source database connection string has not been defined");
            targetConnectionString = connectionString.RawValue;
            Setting dbs = PersistenceProviderManager.Provider.GetByKey(Constants.Settings.EXCLUDED_DATABASES);
            if (dbs != null)
            {
                excludeDatabases.AddRange(dbs.RawValue.Split(new char[] { ',' }));
            }
        }

        public override IList<string> GetServerDatabases()
        {
            using (SqlConnection con = new SqlConnection(targetConnectionString))
            {
                ServerConnection cn = new ServerConnection(con);

                Server server = new Server(cn);
                IList<string> databases = new List<string>();
                foreach (Microsoft.SqlServer.Management.Smo.Database db in server.Databases)
                    if (!excludeDatabases.Contains(db.Name))
                        databases.Add(db.Name);

                return databases;
            }
        }

        public override IList<string> GetServerTables(string database)
        {
            using (SqlConnection con = new SqlConnection(targetConnectionString))
            {
                ServerConnection cn = new ServerConnection(con);

                Server server = new Server(cn);
                IList<string> tables = new List<string>();
                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];

                if (db != null && !excludeDatabases.Contains(db.Name))
                    foreach (Microsoft.SqlServer.Management.Smo.Table tbl in db.Tables)
                        tables.Add(tbl.Name);

                return tables;
            }
        }

        public override IList<string> GetServerViews(string database)
        {
            using (SqlConnection con = new SqlConnection(targetConnectionString))
            {
                ServerConnection cn = new ServerConnection(con);

                Server server = new Server(cn);
                IList<string> views = new List<string>();
                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];
                if (db != null)
                    foreach (Microsoft.SqlServer.Management.Smo.View vw in db.Views)
                        views.Add(vw.Name);

                return views;
            }
        }

        public override IList<string> GetServerStoredProcs(string database)
        {
            using (SqlConnection con = new SqlConnection(targetConnectionString))
            {
                ServerConnection cn = new ServerConnection(con);

                Server server = new Server(cn);
                IList<string> procs = new List<string>();
                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];
                if (db != null)
                    foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure proc in db.StoredProcedures)
                        procs.Add(proc.Name);

                return procs;
            }
        }

        public override IList<string> GetServerStoredProcs(string database, bool includeSystemObjects)
        {
            using (SqlConnection con = new SqlConnection(targetConnectionString))
            {
                ServerConnection cn = new ServerConnection(con);

                Server server = new Server(cn);
                IList<string> procs = new List<string>();
                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];
                if (db != null)
                    foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure proc in db.StoredProcedures)
                        if (proc.IsSystemObject == includeSystemObjects)
                            procs.Add(proc.Name);

                return procs;
            }
        }

        public override IList<string> GetServerColumns(string database, string table)
        {
            using (SqlConnection con = new SqlConnection(targetConnectionString))
            {
                ServerConnection cn = new ServerConnection(con);

                Server server = new Server(cn);
                IList<string> columns = new List<string>();
                if (server.Databases[database] != null)
                {
                    Microsoft.SqlServer.Management.Smo.Table tbl = server.Databases[database].Tables[table];
                    if (null != tbl)
                        foreach (Microsoft.SqlServer.Management.Smo.Column column in tbl.Columns)
                            columns.Add(column.Name);
                }
                return columns;
            }
        }

        public override IList<string> GetServerFunctions(string database)
        {
            using (SqlConnection con = new SqlConnection(targetConnectionString))
            {
                ServerConnection cn = new ServerConnection(con);

                Server server = new Server(cn);
                IList<string> functions = new List<string>();
                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];
                foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction func in db.UserDefinedFunctions)
                    functions.Add(func.Name);

                return functions;
            }
        }

        #region Private Methods
        private static void PopulateDbObject(SqlDataReader reader, DbDoc.BusinessObjects.DbObject obj)
        {
            obj.Id = reader.GetInt32(reader.GetOrdinal("Id"));
            obj.Deleted = reader.GetBoolean(reader.GetOrdinal("Deleted"));
            obj.Name = reader.GetString(reader.GetOrdinal("Name"));
            if (!reader.IsDBNull(reader.GetOrdinal("CommentId")))
                obj.Comment.Id = reader.GetInt32(reader.GetOrdinal("CommentId"));
            if (!reader.IsDBNull(reader.GetOrdinal("Message")))
                obj.Comment.Text = reader.GetString(reader.GetOrdinal("Message"));
            if (!reader.IsDBNull(reader.GetOrdinal("IsSystemObject")))
                obj.Comment.Text = reader.GetString(reader.GetOrdinal("IsSystemObject"));
            if (!reader.IsDBNull(reader.GetOrdinal("SystemInfo")))
                obj.Comment.Text = reader.GetString(reader.GetOrdinal("SystemInfo"));
        }

        #endregion
      
    }
}
